/*
	This proc gives the transferred materail lots information on the day as well in between the date Ranges
	EXEC SP_TRANSFER_MATERIAL_LOT_REPORT '0','0','0','0','03/01/2011','03/31/2011'
*/
ALTER PROCEDURE SP_TRANSFER_MATERIAL_LOT_REPORT
(
	@PROCESS_TYPE		VARCHAR(50),
	@PROCESSSTEP_IDS	VARCHAR(100),
	@BASEPRODUCT_ID		VARCHAR(50),
	@VENDOR			    VARCHAR(100),
	@FROMDATE		    VARCHAR(50),
	@TODATE 		    VARCHAR(50)
)

 AS
 BEGIN
				
	CREATE TABLE #RESULT_TABLE ( [BASE PRODUCT] VARCHAR(50), [PART NUMBER] VARCHAR(50), [LOT NUMBER] VARCHAR(50),[FAB LOT NUMBER] VARCHAR(50),[VENDOR] VARCHAR(50),
						[PROCESS TYPE] VARCHAR(20), [PROCESS TYPE NAME] VARCHAR(20),[TRANSFER DATE] DATETIME, [WAFER QTY] INT, [DIE QTY] INT, [WAFER IDS] VARCHAR(200), 
						[FROM DEPT] VARCHAR(50), [TO DEPT] VARCHAR(50), [FROM DEPT A/C] VARCHAR(50), [TO DEPT A/C] VARCHAR(50), 
						[COMMENTS] VARCHAR(500), [TRANSFER BY] VARCHAR(50), PROCESSTEP_ID INT, [COST] NUMERIC(18,4) )
	
	DECLARE @SQLSTRING	NVARCHAR(4000)
	DECLARE @SQLSTRING1	NVARCHAR(4000)
		
	SET @SQLSTRING = N' INSERT INTO #RESULT_TABLE
						SELECT (SELECT PART_NUMBER FROM PRODUCT WHERE PRODUCT_ID = 
					CASE 
						WHEN ( P.P_PARENT_PRODUCT_ID IS NULL OR P.P_PARENT_PRODUCT_ID = -1 ) THEN P.PRODUCT_ID 
						WHEN P.P_PARENT_PRODUCT_ID IS NOT NULL THEN P.P_PARENT_PRODUCT_ID	
					END 
				),
				P.PART_NUMBER, LOT_NUMBER,FAB_LOT_NUMBER, L_VENDOR_NAME, PS_TYPE, PS_TYPE_NAME, TRANSFERRED_DATE,TRANSFERRED_WAFER_QTY,
				 TRANSFERRED_DIE_QTY,TRANSFERRED_WAFER_IDS, TRANSFERRED_FROM,TRANSFERRED_TO, DT.Full_Account, DTT.FULL_ACCOUNT, Comments,ISNULL(TRANSFERRED_UPDATE_BY, TRANSFERRED_BY), P.ProcessStep_ID, null
				FROM TRANSFERRED_LOTS TL LEFT OUTER JOIN Department_Table DT ON DT.DEPARTMENT_NAME = TL.Transferred_FROM LEFT OUTER JOIN DEPARTMENT_TABLE DTT ON DTT.DEPARTMENT_NAME = TL.Transferred_TO, LOT_TABLE LT, PRODUCT P, PROCESS_STEP PS, LOCATION L
				WHERE TL.Lot_ID = Lt.Lot_ID AND  LT.PROCESSSTEP_ID = PS.PROCESSSTEP_ID AND PS.PROCESSSTEP_ID = P.PROCESSSTEP_ID
				AND LT.LOCATION_ID = L.LOCATION_ID'
	
	SET @SQLSTRING1 = ''
	
	IF ( @PROCESS_TYPE IS NOT NULL AND @PROCESS_TYPE <> '0' )
	BEGIN
		SET @SQLSTRING1 = @SQLSTRING1 + ' AND PS.PS_TYPE = ''' +  @PROCESS_TYPE + '''' 
	END
		
	IF ( @PROCESSSTEP_IDS IS NOT NULL AND @PROCESSSTEP_IDS <> '0' )
	BEGIN
		SET @SQLSTRING1 = @SQLSTRING1 + ' AND LT.PROCESSSTEP_ID = ''' +  @PROCESSSTEP_IDS + '''' 
	END 
	
	IF ( @BASEPRODUCT_ID IS NOT NULL AND @BASEPRODUCT_ID <> '0' )
	BEGIN
		SET @SQLSTRING1 = @SQLSTRING1 + ' AND P_PARENT_PRODUCT_ID =  ''' +  @BASEPRODUCT_ID + '''' 
	END
	
	IF ( @VENDOR IS NOT NULL AND @VENDOR <> '0' )
	BEGIN
		SET @SQLSTRING1 = @SQLSTRING1 + ' and L.VENDOR_ID IN ( SELECT PROCESSSTEP_ID FROM FN_GET_PROCESS_STEPS_IN_STRING (  ''' + @VENDOR + '''' +') )'
	END

	SET @SQLSTRING1 = @SQLSTRING1 + ' AND TL.TIMESTAMP BETWEEN '''+ CONVERT(VARCHAR,@FROMDATE, 101)+''' AND '''+CONVERT(VARCHAR,@TODATE, 101)+''' '	

	--PRINT @SQLSTRING
	--PRINT @SQLSTRING1
	EXEC (@SQLSTRING + @SQLSTRING1)

	DECLARE @PROCESSSTEP_CURSOR CURSOR
	DECLARE @TRANSFER_PROCESSTEP_ID INT
	DECLARE @CURR_QUARTER_NO INT
	DECLARE @CURRENT_YEAR INT
	DECLARE @TRANSFER_DATE DATETIME
	DECLARE @STD_COST NUMERIC(18,4)
	DECLARE @PS_TYPE_NAME VARCHAR(50)
	--SET @CURRENT_YEAR = YEAR( GETDATE() )

	SET @PROCESSSTEP_CURSOR = CURSOR FOR SELECT PROCESSTEP_ID, [PROCESS TYPE NAME], [TRANSFER DATE] FROM #RESULT_TABLE
	OPEN @PROCESSSTEP_CURSOR 
	FETCH NEXT FROM @PROCESSSTEP_CURSOR INTO @TRANSFER_PROCESSTEP_ID, @PS_TYPE_NAME, @TRANSFER_DATE
	WHILE @@FETCH_STATUS = 0
    BEGIN
		SELECT @CURR_QUARTER_NO = CQC_QUARTER_NO, @CURRENT_YEAR = YEAR( CQC_START_DATE ) FROM COMPANY_QUARTERLY_CALENDAR 
			WHERE @TRANSFER_DATE >= CQC_START_DATE AND @TRANSFER_DATE <= CQC_END_DATE

		SELECT @STD_COST = (CASE WHEN @CURR_QUARTER_NO = 1 THEN Q1_Standard_Cost WHEN @CURR_QUARTER_NO = 2 THEN Q2_Standard_Cost WHEN @CURR_QUARTER_NO = 3 THEN Q3_Standard_Cost ELSE Q4_STANDARD_COST END) FROM Update_View_Cost_Table WHERE ProcessStep_ID = @TRANSFER_PROCESSTEP_ID and YEAR(FIN_YEAR) = @CURRENT_YEAR

		IF ( @PS_TYPE_NAME != 'FAB' AND @PS_TYPE_NAME != 'Wafer Store' AND @PS_TYPE_NAME != 'SORT' )
		BEGIN
			UPDATE #RESULT_TABLE SET [COST] = [DIE QTY]*@STD_COST  WHERE PROCESSTEP_ID = @TRANSFER_PROCESSTEP_ID AND [TRANSFER DATE] = @TRANSFER_DATE
		END
		ELSE
		BEGIN
			UPDATE #RESULT_TABLE SET [COST] = [WAFER QTY]*@STD_COST  WHERE PROCESSTEP_ID = @TRANSFER_PROCESSTEP_ID AND [TRANSFER DATE] = @TRANSFER_DATE
		END
		
		SET @TRANSFER_PROCESSTEP_ID = 0
		SET @PS_TYPE_NAME = NULL
		SET @TRANSFER_DATE = NULL
		SET @STD_COST  = NULL
		FETCH NEXT FROM @PROCESSSTEP_CURSOR INTO @TRANSFER_PROCESSTEP_ID, @PS_TYPE_NAME, @TRANSFER_DATE
	 END
	CLOSE @PROCESSSTEP_CURSOR
	DEALLOCATE @PROCESSSTEP_CURSOR
	
	SELECT  [PART NUMBER],[BASE PRODUCT], [LOT NUMBER],[FAB LOT NUMBER],[VENDOR],[PROCESS TYPE], [TRANSFER DATE], [WAFER QTY], [DIE QTY], [WAFER IDS], 
						[FROM DEPT], [TO DEPT], [FROM DEPT A/C], [TO DEPT A/C], [COST],	[COMMENTS], [TRANSFER BY] FROM #RESULT_TABLE Order BY [BASE PRODUCT], [PART NUMBER], [TRANSFER DATE]
END